package com.webber.demo.common.utils.poi;

import java.beans.IntrospectionException;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.webber.demo.common.utils.FileUtil;
import com.webber.demo.common.utils.InvokeUtil;
/**
 * 
 * @author zzp
 * @dete  2017-07-13 
 * @Description 重写Excel工具类，支持2003,2007导入导出,最底下三个方法不是我写的，我也不知道是干啥的，没加注释
 * 
 */
public class  ExcelUtil <T> {
	public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy-MM-dd";//默认日期格式
    /**
     * 列宽度
     */
    public static int DEFAULT_COLOUMN_WIDTH = 17; 
	public static final String HEADERINFO="headInfo";  
    public static final String DATAINFON="dataInfo";
    /**
     * 2003格式扩展名
     */
    public static final String EXCEL_2003_EXTENDS_NAME=".xls";
    /**
     * 2007格式扩展名
     */
    public static final String EXCEL_2007_EXTENDS_NAME=".xlsx";
    /**
     * 一个sheet默认有多少条数据
     * excel2003版本一个工作表最多65536
     * excel2007版本一个工作表最多1048576
     */
    public static final int DEFAULT_SHEETSIZE=65536;
    /** 
     *  
     * @Description: (根据传入的文件名获取工作簿对象(Workbook)) 
     * @param filename 文件扩展名 两种分别是 xls和xlsx
     * @return 
     * @throws IOException  
     */  
    public static Workbook getWeebWork(String fileName) throws IOException{  
        Workbook workbook=null;  
        if(null!=fileName){  
            String fileType=fileName.substring(fileName.lastIndexOf("."),fileName.length());  
            FileInputStream fileStream = new FileInputStream(new File(fileName));  
            if(EXCEL_2003_EXTENDS_NAME.equals(fileType.trim().toLowerCase())){  
                workbook = new HSSFWorkbook(fileStream);// 创建 Excel 2003 工作簿对象  
            }else if(EXCEL_2007_EXTENDS_NAME.equals(fileType.trim().toLowerCase())){  
                workbook = new XSSFWorkbook(fileStream);//创建 Excel 2007 工作簿对象  
            }  
        }  
        return workbook;  
    }  
    
    /**
     * @Description: (根据传入的文件名获取工作簿对象(Workbook)) 
     * @param in 输入流
     * @param filename 文件扩展名 两种分别是 .xls和.xlsx
     * @return Workbook
     * @throws IOException
     */
    private static Workbook getWeebWork(InputStream in,String fileName) throws IOException{  
        Workbook workbook=null;  
        if(null!=fileName){  
        	if(EXCEL_2003_EXTENDS_NAME.equals(fileName)) {
        		POIFSFileSystem fs = new POIFSFileSystem(in); 
				workbook = new HSSFWorkbook(fs);// 创建 Excel 2003 工作簿对象  
        	}
        	if(EXCEL_2007_EXTENDS_NAME.equals(fileName)) {
				workbook = new XSSFWorkbook(in);//创建 Excel 2007 工作簿对象
        	}
        }  
        in.close();
        return workbook; 
    }
    /**
     * @Description: (根据传入的文件名获取工作簿对象(Workbook)) 
     * @param mFile
     * @param fileName 文件扩展名 两种分别是 .xls和.xlsx
     * @throws IOException
     */
    public static Workbook getWeebWork(MultipartFile mFile,String fileName) throws IOException{  
    	InputStream in = mFile.getInputStream();
        return getWeebWork(in, fileName); 
    }
    /** 
     *  
     * @Title: writeExcel 
     * @Description:(导出Excel表) 
     * @example	map.put(ExcelUtil.HEADERINFO,List<String> headList); map.put(ExcelUtil.DATAINFON,List<TreeMap<String,Object>>  dataList); 
     * @param pathname:导出Excel表的文件路径 
     * @param map：封装需要导出的数据(HEADERINFO封装表头信息，DATAINFON：封装要导出的数据信息,此处需要使用TreeMap) 
     * @throws IOException 
     */  
    @SuppressWarnings("unchecked")
	public static void writeExcel(String pathname,Map<String,Object> map,Workbook wb) throws IOException{  
        if(null!=map && null!=pathname){  
            List<Object> headList = (List<Object>) map.get(ExcelUtil.HEADERINFO);  
            List<TreeMap<String,Object>> dataList =  (List<TreeMap<String, Object>>) map.get(ExcelUtil.DATAINFON);  
            CellStyle style = getCellStyle(wb);  
            Sheet sheet = wb.createSheet();  
            /** 
             * 设置Excel表的第一行即表头 
             */  
            Row row =sheet.createRow(0);  
            for(int i=0;i<headList.size();i++){  
                Cell headCell = row.createCell(i);  
                headCell.setCellType(Cell.CELL_TYPE_STRING);  
                headCell.setCellStyle(style);//设置表头样式  
                headCell.setCellValue(String.valueOf(headList.get(i)));  
            }  
              
            for (int i = 0; i < dataList.size(); i++) {  
                Row rowdata = sheet.createRow(i+1);//创建数据行  
                TreeMap<String, Object> mapdata =dataList.get(i);  
                Iterator<String> it = mapdata.keySet().iterator();  
                int j=0;  
                while(it.hasNext()){  
                    String strdata = String.valueOf(mapdata.get(it.next()));  
                    Cell celldata = rowdata.createCell(j);  
                    celldata.setCellType(Cell.CELL_TYPE_STRING);  
                    celldata.setCellValue(strdata);  
                    j++;  
                }  
            }  
            File file = new File(pathname);  
            OutputStream os = new FileOutputStream(file);  
            os.flush();  
            wb.write(os);  
            os.close();  
        }  
    }  
    
  /**
   * 把map里面的数据填充到OutputStream
   * @param title 标题
   * @param headMap 表头
   * @param dataList 数据列表
   * @param colWidth 列宽 写0代表默认
   * @param sheetSize 每个sheet可以存的条数
   * @param os
   * @throws IOException
   */
    public static void createExcel(String title,Map<String, String> headMap,List<Map<String,Object>> dataList,int colWidth,int sheetSize,OutputStream os) throws IOException{  
    	Workbook wb = new XSSFWorkbook();
    	if(sheetSize==0){sheetSize=DEFAULT_SHEETSIZE;}
        if(null!=headMap&&null!=dataList){  
            //表头样式
            CellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = wb.createCellStyle();
            headerStyle.setFillPattern(CellStyle.NO_FILL);
            headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
            headerStyle.setBorderLeft( CellStyle.BORDER_THIN);
            headerStyle.setBorderRight( CellStyle.BORDER_THIN);
            headerStyle.setBorderTop( CellStyle.BORDER_THIN);
            headerStyle.setAlignment( CellStyle.ALIGN_CENTER);
            Font headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFillPattern(CellStyle.NO_FILL);
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyle.setBorderLeft( CellStyle.BORDER_THIN);
            cellStyle.setBorderRight( CellStyle.BORDER_THIN);
            cellStyle.setBorderTop( CellStyle.BORDER_THIN);
            cellStyle.setAlignment( CellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment( CellStyle.VERTICAL_CENTER);
            Font cellFont = wb.createFont();
            cellFont.setBoldweight( Font.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            Sheet sheet = wb.createSheet();
            // 声明一个画图的顶级管理器
            //设置列宽
            int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
        
         // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Map<String,Object> mp: dataList) {
            	   if(rowIndex == sheetSize || rowIndex == 0){
                       if ( rowIndex != 0 ) sheet = wb.createSheet();//如果数据超过了，则在第二页显示
                       Row titleRow = sheet.createRow(0);//表头 rowIndex=0
                       titleRow.createCell(0).setCellValue(title);
                       titleRow.getCell(0).setCellStyle(titleStyle);
                       sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                       Row headerRow = sheet.createRow(1); //列头 rowIndex =1
                       for(int i=0;i<headers.length;i++)
                       {
                           headerRow.createCell(i).setCellValue(headMap.get(headers[i]));
                           headerRow.getCell(i).setCellStyle(headerStyle);
                       }
                       int j = 0;
                       for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
                           String key = iter.next();
                           properties[j] = key;
                           headers[j] = key;
                           int bytes = key.getBytes().length;
                           arrColWidth[j] =  bytes < minBytes ? minBytes : bytes;
                           sheet.setColumnWidth(j,arrColWidth[j]*2*256);
                           j++;
                       }
                       rowIndex = 2;//数据内容从 rowIndex=2开始
                   }
                Row dataRow = sheet.createRow(rowIndex);
                for (int i = 0; i < properties.length; i++)
                {
                    Cell newCell = dataRow.createCell(i);

                    Object o =  mp.get(properties[i]);
                    String cellValue = ""; 
                    if(o==null) cellValue = "";
                    else if(o instanceof Date) cellValue = new SimpleDateFormat(DEFAULT_DATE_PATTERN).format(o);
                    else cellValue = o.toString();
                    newCell.setCellValue(cellValue);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
			}
           for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            try {
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }  
    }
    /**
     * 传入数据list，表头，下载excel,参照testDownload()这个方法使用
     * @param title 标题，文件名
     * @param headMap 表头
     * @param dataList 数据 
     * @param response
     */
    public static void downloadExcelFile(String title,Map<String,String> headMap,List<Map<String,Object>> dataList,HttpServletResponse response){
     	 ServletOutputStream outputStream=null;
     	 BufferedInputStream bis =null;
         BufferedOutputStream bos = null;
     	try {
             ByteArrayOutputStream os = new ByteArrayOutputStream();
             createExcel(title, headMap, dataList, 0, 0, os);
             byte[] content = os.toByteArray();
             InputStream is = new ByteArrayInputStream(content);
             // 设置response参数，可以打开下载页面
             response.reset();
             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
             response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"));
             response.setContentLength(content.length);
             outputStream = response.getOutputStream();
             bis = new BufferedInputStream(is);
             bos = new BufferedOutputStream(outputStream);
             byte[] buff = new byte[8192];
             int bytesRead;
             while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                 bos.write(buff, 0, bytesRead);
             }
             outputStream.flush();
         }catch (Exception e) {
             e.printStackTrace();
         }finally{
         	 try {
         		 if(bos!=null){
         			 bos.close();
         		 }
         		 if(bis!=null){
         			 bis.close();
         		 }
         		 if(outputStream!=null){
         			 outputStream.close();
         		 }
 			} catch (IOException e) {
 				e.printStackTrace();
 			}
         }
     }
    /** 
     *  
     * @Title: getCellStyle 
     * @Description: TODO（设置表头样式） 
     * @param wb 
     * @return 
     */  
    public static CellStyle getCellStyle(Workbook wb){  
        CellStyle style = wb.createCellStyle();  
        Font font = wb.createFont();  
        font.setFontName("宋体");  
        font.setFontHeightInPoints((short)12);//设置字体大小  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗  
        style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);//让单元格居中  
        //style.setWrapText(true);//设置自动换行  
        style.setFont(font);  
        return style;  
    }  
    /**
	 * @Description 读取Excel的内容
	 * @param file 读取数据的源Excel
	 * @param rowsStart 从哪一行开始读取
	 * @return 以二维数组的形式返回读出结果，外层数字行记录，内层列记录
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String[][] readerExcel(Workbook workbook,int rowsStart) throws FileNotFoundException, IOException {
		if(workbook!=null) {
			List<String[]> result = new ArrayList<String[]>();
			int rowSize = 0;
			// 打开HSSFWorkbook
			Workbook wb=workbook;
			Cell cell = null;
			for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
				Sheet st = wb.getSheetAt(sheetIndex);
				// 第一行为标题，不取
				for (int rowIndex = rowsStart; rowIndex <= st.getLastRowNum(); rowIndex++) {
					Row row = st.getRow(rowIndex);
					if (row == null) {
						continue;
					}
					int tempRowSize = row.getLastCellNum() + 1;
					if (tempRowSize > rowSize) {
						rowSize = tempRowSize;
					}
					String[] values = new String[rowSize];
					Arrays.fill(values, "");
					boolean hasValue = false;
					for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
						String value = "";
						cell = row.getCell(columnIndex);
						if (cell != null) {
							// 注意：一定要设成这个，否则可能会出现乱码
							// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
							switch (cell.getCellType()) {
								case HSSFCell.CELL_TYPE_STRING:		
									value = cell.getStringCellValue();
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									if (HSSFDateUtil.isCellDateFormatted(cell)) {
										Date date = cell.getDateCellValue();
										if (date != null) {
											value = new SimpleDateFormat("yyyy-MM-dd").format(date);
										} else {
											value = "";
										}
									} else {
										value = new DecimalFormat("0").format(cell.getNumericCellValue());
									}
									break;
								case HSSFCell.CELL_TYPE_FORMULA:
									// 导入时如果为公式生成的数据则无值
									if (!cell.getStringCellValue().equals("")) {
										value = cell.getStringCellValue();
									} else {
										value = cell.getNumericCellValue() + "";
									}
									break;
								case HSSFCell.CELL_TYPE_BLANK:
									break;
								case HSSFCell.CELL_TYPE_ERROR:
									value = "";
									break;
								case HSSFCell.CELL_TYPE_BOOLEAN:
									value = (cell.getBooleanCellValue() == true ? "Y" : "N");
									break;
								default:
									value = "";
								}
						}
						if (columnIndex == 0 && value.trim().equals("")) {
							break;
						}
						values[columnIndex] = rightTrim(value);
						hasValue = true;
					}
					if (hasValue) {
						result.add(values);
					}
				}
			}
			String[][] returnArray = new String[result.size()][rowSize];
			for (int i = 0; i < returnArray.length; i++) {
				returnArray[i] = (String[]) result.get(i);
			}
			return returnArray;
		}
		return null;
	}
	/**
	 * 读取request excel数据
	 * @param request
	 * @return 返回读取的数据，返回类型是map，其中key是文件名
	 * @throws IOException
	 */
	public static Map<String,String[][]> readExcelWidthFileName(HttpServletRequest request,int rowsStart ) throws IOException {
		List<Map<String,Object>> list = handleRequest(request);
		Map<String,String[][]> returnMap = null;
		if (list.size() > 0) {
			returnMap = new HashMap<String,String[][]>();
			for (int i = 0 ; i < list.size() ; i++) {
				Map<String,Object> map = list.get(i);
				MultipartFile mFile = (MultipartFile) map.get("file");
				String fileName = (String) map.get("name");
				String fileType=fileName.substring(fileName.lastIndexOf("."),fileName.length()).toLowerCase();  
				String[][] result=readerExcel(getWeebWork(mFile.getInputStream(),fileType), rowsStart);
				returnMap.put(fileName, result);
			}
		}
		return returnMap;	
	}
	/**
	 * 读取request excel数据
	 * @param request
	 * @return 返回读取的数据，不带文件名称
	 * @throws IOException
	 */
	public static String[][] readExcelNoFileName(HttpServletRequest request,int rowsStart ) throws IOException {
		List<Map<String,Object>> list = handleRequest(request);
		String[][] result=null;
		if (list.size() > 0) {
			for (int i = 0 ; i < list.size() ; i++) {
				Map<String,Object> map = list.get(i);
				MultipartFile mFile = (MultipartFile) map.get("file");
				String fileName = (String) map.get("name");
				String fileType=fileName.substring(fileName.lastIndexOf("."),fileName.length()).toLowerCase();  
				result=readerExcel(getWeebWork(mFile.getInputStream(),fileType), rowsStart);
			}
		}
		return result;	
	}
	

    /**
     * 反射把解析结果存入javaBean
     * @param list 结果集合
     * @param clazz javaBean.class clazz 实体bean属性全部String类型
     * @return
     * @throws IntrospectionException 
     * @throws IOException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     * @throws Exception
     */
	 public static <T> List<T> excel2Beans(HttpServletRequest request,int startIndex,String[] headAttrName,Class<T> clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException, IOException, IntrospectionException{
		 if(headAttrName==null) {
			 return null;
		 }
		 List<Map<String,Object>> list = handleRequest(request);	
		 List<T> lists = null;
		 if (list.size() > 0) {
				for (int i = 0 ; i < list.size() ; i++) {
					Map<String,Object> map = list.get(i);
					MultipartFile mFile = (MultipartFile) map.get("file");
					String fileName = (String) map.get("name");
					String fileType=fileName.substring(fileName.lastIndexOf("."),fileName.length()).toLowerCase();  
					lists=excel2Beans(mFile.getInputStream(), startIndex, fileType, headAttrName, clazz);
				}
			}
	    return lists;
	   }
	/**
	 * 去掉字符串右边的空格
	 * @param str 要处理的字符串
	 * @return 处理后的字符串
	 */
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}
      
    private  void mytest() throws IOException { 
    	String[][] resyule=readerExcel(getWeebWork("e:/myexcel.xlsx"), 1);
    	for (String[] strings : resyule) {
			for (String string : strings) {
				System.out.print(string+" \t");
			}
			System.out.println();
		}
    }  
    
    
    
    /**
     * 将workbook中的值放入List<Map<String,Object>>结构中
     * @param workbook 工作簿
     * @param startIndex 从第几行开始读取
     * @param columnName 表头名称对应的实体类属性字段
     * @return
     */
    public static List<Map<String, Object>> parseExcel(Workbook workbook,int startIndex,String[] columnName){
    	if(columnName==null||columnName.length!=workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells()) {
    		return null;
    	}
    	List<Map<String, Object>> result = new LinkedList<Map<String,Object>>();
    	for(int sheetIndex=0;sheetIndex < workbook.getNumberOfSheets();sheetIndex++){	//sheet
    		Sheet sheet = (Sheet) workbook.getSheetAt(sheetIndex);
    		for(int rowIndex=startIndex;rowIndex < sheet.getPhysicalNumberOfRows();rowIndex++){	//row
    			Row row = sheet.getRow(rowIndex);
    			Map<String, Object> map = new HashMap<String, Object>();
    			for(int cellIndex=0;cellIndex < row.getPhysicalNumberOfCells();cellIndex++){ //cell
    				Cell cell = row.getCell(cellIndex);
    				if(columnName[cellIndex]!=null && columnName[cellIndex].trim().length()>0){ //该列值在对应的java对象中有值
    					//取出当前cell的值和对应Javabean类的属性放入到map中
    					map.put(columnName[cellIndex].trim(), getCellValue(cell));  
    				}
    			}
    			result.add(map);
    		}
    	}
    	return result;
    }
    

    /**
     * 反射把解析结果存入javaBean
     * @param list 结果集合
     * @param clazz javaBean.class
     * @return
     * @throws IntrospectionException 
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws Exception
     */
    public static <T>  List<T> toObjectList(List<Map<String, Object>> list,Class<T> clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException, IntrospectionException {
    	if(list==null) {
    		return null;
    	}
    	List<T> returnList = new LinkedList<T>();
    	for(int i=0;i<list.size();i++){
    		Map<String, Object> objMp =list.get(i); 
    		T obj = InvokeUtil.convertMap(clazz, objMp);
    	
			returnList.add(obj);
    	}
    	return returnList;
    }
    /**
     * 
     * @param workbook
     * @param startIndex 从哪一行开始读取
     * @param propertiesName 列和
     * @param clazz 实体bean属性全部String类型
     * @return
     * @throws IOException 
     * @throws IntrospectionException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     * @throws Exception
     */
    public static <T> List<T> excel2Beans(InputStream in,int startIndex,String extendsName,String[] headAttrName,Class<T> clazz) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException, IntrospectionException {
    	if(in==null||extendsName==null||headAttrName==null) {
    		return null;
    	}
    	Workbook workbook = getWeebWork(in,extendsName);
    	List<T> lists = null;
    	List<Map<String, Object>> dataList =  parseExcel(workbook,startIndex,headAttrName);
    	if(dataList!=null) {
    		lists=toObjectList(dataList, clazz);
    	}
    	return lists;
   }

    
    /**
     * 获取当前单元格内容
     * */
    private static String getCellValue(Cell cell){
    	String value = "";
    	if(cell!=null){
    		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:		
				value = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					if (date != null) {
						value = new SimpleDateFormat("yyyy-MM-dd").format(date);
					} else {
						value = "";
					}
				} else {
					value = new DecimalFormat("0").format(cell.getNumericCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				// 导入时如果为公式生成的数据则无值
				if (!cell.getStringCellValue().equals("")) {
					value = cell.getStringCellValue();
				} else {
					value = cell.getNumericCellValue() + "";
				}
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				value = "";
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				value = (cell.getBooleanCellValue() == true ? "Y" : "N");
				break;
			default:
				value = "";
			}
    	}
    	return value;
    }
    
/*    
 * 参照这个方法导出excel使用
 * private  void testDownload(){
    	String fileName =  "测试excel";
		Map<String,String> headMap = new LinkedHashMap<String,String>();
        headMap.put("name","姓名");
        headMap.put("age","年龄");
        headMap.put("birthday","生日");
        headMap.put("height","身高");
        headMap.put("weight","体重");
        headMap.put("sex","性别");
        int count = 10;
        List<Map<String, Object>> ja = new ArrayList<Map<String, Object>>();
        for(int i=0;i<count;i++){
        Map<String, Object> ob = new HashMap<String, Object>();
       	 ob.put("name","姓名"+i);
       	 ob.put("age","年龄"+i);
       	 ob.put("birthday","生日"+i);
       	 ob.put("height","身高"+i);
       	 ob.put("weight","体重"+i);
       	 ob.put("sex","性别"+i);
       	 ja.add(ob);
        }
        ExcelUtil.downloadExcelFile(fileName, headMap, ja, response);
    }*/
    /**
     * 
     * @param request
     * @return
     */
	public static List<Map<String,Object>> handleRequest (HttpServletRequest request) {
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Map<String,Object> map = new HashMap<String,Object>();
		MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest)request;
	      for (Iterator<String> it = mRequest.getFileNames(); it.hasNext();) {
		        String key = (String)it.next();
		        MultipartFile mFile = mRequest.getFile(key);
		        String fileName = mFile.getOriginalFilename();
		        String fileExt = FileUtil.getFileExt(fileName).toLowerCase();
		        if ((fileExt == null) || (fileExt.length() == 0)) {
		          continue;
		        }
		        if ((!"jpg".equals(fileExt)) && (!"png".equals(fileExt)) && (!"psd".equals(fileExt)) && (!"doc".equals(fileExt)) && ("docx".equals(fileExt)) && ("xls".equals(fileExt)) && ("xlsx".equals(fileExt)) && ("ppt".equals(fileExt)) && ("rar".equals(fileExt)) && ("zip".equals(fileExt)) && ("txt".equals(fileExt)) && ("pdf".equals(fileExt)))
		        {
		         continue;
		       }
		       if(EXCEL_2003_EXTENDS_NAME.indexOf(fileExt)!=-1||EXCEL_2007_EXTENDS_NAME.indexOf(fileExt)!=-1) {
		    	   map.put("file", mFile);
			       map.put("name", fileName);
		       }
		        
	      }
	      if (!map.isEmpty()) {
	    	  list.add(map);
	      }
	     return list;
	}
	

	public static void transferMapFromExcel (HttpServletRequest request,List<Map<String,Object>> recList) throws IOException {
		List<Map<String,Object>> list = handleRequest(request);
		if (list.size() > 0) {
			for (int i = 0 ; i < list.size() ; i++) {
				Map<String,Object> map = list.get(i);
				MultipartFile mFile = (MultipartFile) map.get("file");
				String fileName = (String) map.get("name");
				recList.add(readMapFromExcel(mFile,fileName));
			}
		}	
	}
	
	public static Map<String,Object> readMapFromExcel(MultipartFile mFile,String fileName) throws IOException {
		InputStream is = mFile.getInputStream();
		 Workbook workbook =new XSSFWorkbook(is);
		 
		 int sheetNum =workbook.getNumberOfSheets();
		 Sheet sheet = null;
		 int rows = 0;
		 Map<String,Object> resultMap = new HashMap<String,Object>();
		 for (int i = 0; i<sheetNum; i++) {
			 sheet = workbook.getSheetAt(i);
			 String sheetName = sheet.getSheetName();
			 rows = sheet.getLastRowNum();
			 List<List<String>> outList = new ArrayList<List<String>>();
			 for (int j = 1 ; j<rows ; j++) {
				 Row row = sheet.getRow(j);
				 int colomns = row.getLastCellNum();
				 List<String> innerList = new ArrayList<String>();
				 for (int k = 0; k < colomns ; k++) {
					 Cell cell = row.getCell(k);
					
					 if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						 innerList.add(cell.getStringCellValue());//行数据
					 }
				 }
				 outList.add(innerList);// 表数据
			 }
			 resultMap.put(sheetName, outList);// 表数据
		 }
		 return resultMap;
	}
}
